﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using JVP.Infrastructure;
using System.Data.SqlClient;
using System.Data;

namespace JVP.Middleware
{
    public class OfficeManager : IOfficeManager
    {
        #region IOfficeManager Members

        /// <summary>
        /// To Check the user valid or not
        /// </summary>
        /// <param name="userName"><see cref="System."/></param>
        /// <param name="passWord"></param>
        /// <param name="cmd"></param>
        /// <returns></returns>

        public bool isValidUser(string userName, string passWord, SqlCommand cmd)
        {
            cmd.CommandText = "Select * from OfficeStaffDetails where EmpCode='" + userName + "' and Password='" + passWord + "'";
            SqlDataReader res = cmd.ExecuteReader();
            if (res.HasRows)
            {
                res.Close();
                cmd.CommandText = "Insert into LoginDetails(EmpCode,Password,LoginTime) values('" + userName + "','" + passWord + "','" + DateTime.Now + "')";
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                return true;
            }
            else
                return false;
        }

        #endregion

        #region IOfficeManager Members

        /// <summary>
        /// To get the plans which has in active status
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>

        public SqlDataReader getPlans(SqlCommand cmd)
        {
            cmd.CommandText = "Select * from PlanDetails where PlanStatus='Active'";
            SqlDataReader dr = cmd.ExecuteReader();
            return dr;
        }

        #endregion

        #region IOfficeManager Members

        /// <summary>
        /// To add a new plan into the database
        /// </summary>
        /// <param name="planName"><see cref="System.String"/> that holds the name of the plan</param>
        /// <param name="planTerm"><see cref="System.String"/> that holds the term of the plan</param>
        /// <param name="planType"><see cref="System.String"/> that holds the type of the plan</param>
        /// <param name="description"><see cref="System.String"/> that holds the description about the plan</param>
        /// <param name="cmd"><see cref="System.Data.SqlClient"/> that holds the command of the connection</param>
        /// <returns>it returns true when the command executed correctly otherwise returns false</returns>

        public bool addPlan(string planName, string planTerm, string planType, string planStatus, string description, SqlCommand cmd)
        {
            cmd.CommandText = "Insert into PlanDetails values('" + planName + "','" + planTerm + "','" + planType + "','" + planStatus + "','" + description + "')";
            int res = cmd.ExecuteNonQuery();
            if (res == 1)
                return true;
            else
                return false;
        }

        #endregion

        #region IOfficeManager Members

        /// <summary>
        /// To get the plan details for the particluar plan id
        /// </summary>
        /// <param name="planID"><see cref="System.String"/> that holds the id of the plan</param>
        /// <param name="cmd"><see cref="System.Data.SqlClient"/> that holds the command of the sql connection</param>
        /// <returns>the result of the plan details for the particular plan id.</returns>

        public SqlDataReader getPlan(string planID, SqlCommand cmd)
        {
            cmd.CommandText = "Select * from PlanDetails where PlanID=" + planID;
            SqlDataReader dr = cmd.ExecuteReader();
            return dr;
        }

        #endregion

        #region IOfficeManager Members

        /// <summary>
        /// To Update the Plan Details
        /// </summary>
        /// <param name="planID"><see cref="System.String"/> that holds the id of the plan</param>
        /// <param name="planName"><see cref="System.String"/> that holds the name of the plan</param>
        /// <param name="planTerm"><see cref="System.String"/> that holds the term of the plan</param>
        /// <param name="description"><see cref="System.String"/> that holds the description of the plan</param>
        /// <param name="cmd"><see cref="System.Data.SqlClient"/> that holds the command of the sql connection</param>
        /// <returns><see cref="System.Boolean"/> returns true when query executed successfully otherwise false.</returns>

        public bool editPlan(string planID, string planName, string planTerm, string planType, string planStatus, string description, SqlCommand cmd)
        {
            cmd.CommandText = "Update PlanDetails Set PlanName='" + planName + "', PlanTerm='" + planTerm + "', PlanType='" + planType + "', PlanStatus='" + planStatus + "', Description='" + description + "' where PlanID=" + planID;
            int res = cmd.ExecuteNonQuery();
            if (res == 1)
                return true;
            else
                return false;            
        }

        #endregion

        #region IOfficeManager Members

        /// <summary>
        /// To add a new scheme into the database
        /// </summary>
        /// <param name="schemeDues"><see cref="System.Int32"/> that holds the number of the dues in that scheme</param>
        /// <param name="planYear"><see cref="System.String"/> that holds the duration of the scheme</param>
        /// <param name="amtInstall"><see cref="System.String"/> that holds the installment amount of the scheme</param>
        /// <param name="amtConsi"><see cref="System.String"/> that holds the consideration amount of the scheme</param>
        /// <param name="amtExpConsi"><see cref="System.String"/> that holds the expected consideration of the scheme</param>
        /// <param name="amtAccConsi"><see cref="System.String"/> that holds the accedential consideration of the scheme</param>
        /// <param name="plotSize"><see cref="System.String"/> that holds the size of the plot</param>
        /// <param name="schemeStatus"><see cref="System.String"/> that holds the current status of the scheme</param>
        /// <param name="planID"><see cref="System.String"/> that holds the id of the plan</param>
        /// <param name="cmd"><see cref="System.Data.SqlClient"/> that holds the command of the Sql Connection</param>
        /// <returns>It returns true when the query executed correctly otherwise returns false.</returns>
        
        public bool addScheme(int schemeDues, string planYear, string amtInstall, string amtConsi, string amtExpConsi, string amtAccConsi, string plotSize, string schemeStatus, string planID, SqlCommand cmd)
        {
            cmd.Parameters.Add("@SchemeDues", SqlDbType.Int).Value=schemeDues;
            cmd.Parameters.Add("@PlanYear", SqlDbType.NVarChar).Value = planYear;
            cmd.Parameters.Add("@Install", SqlDbType.Money).Value = String.Format("{0:c}", amtInstall);
            cmd.Parameters.Add("@Consi", SqlDbType.Money).Value = String.Format("{0:c}", amtConsi);
            cmd.Parameters.Add("@ExpConsi", SqlDbType.Money).Value = String.Format("{0:c}", amtExpConsi);
            cmd.Parameters.Add("@AccConsi", SqlDbType.Money).Value = String.Format("{0:c}", amtAccConsi);
            cmd.Parameters.Add("@PlotSize", SqlDbType.BigInt).Value = plotSize;
            cmd.Parameters.Add("@Status", SqlDbType.NVarChar).Value = schemeStatus;
            cmd.Parameters.Add("@planID", SqlDbType.Int).Value = planID;
            cmd.Parameters.Add("@Mode", SqlDbType.Int).Value = 1;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "AddScheme";
            int res = cmd.ExecuteNonQuery();
            if (res == 1)
                return true;
            else
                return false;
        }

        #endregion

        #region IOfficeManager Members

        /// <summary>
        /// To get the schemes from database
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>

        public SqlDataAdapter getSchemes(SqlCommand cmd)
        {
            cmd.CommandText = "Select * from SchemeDetails Order By PlanID";
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            return da;
        }

        #endregion

        #region IOfficeManager Members

        /// <summary>
        /// To get the branches from database.
        /// </summary>
        /// <param name="cmd"><see cref="System.Data.SqlClient"/> that holds the command of the Sql Connection</param>
        /// <returns></returns>

        public SqlDataReader getBranches(SqlCommand cmd)
        {
            cmd.CommandText = "Select * from BranchDetails";
            SqlDataReader dr = cmd.ExecuteReader();
            return dr;
        }

        #endregion

        #region IOfficeManager Members

        /// <summary>
        /// To get the branch detail of the particular branch id
        /// </summary>
        /// <param name="branchID"><see cref="System.String"/> that holds the id of the branch</param>
        /// <param name="cmd"><see cref="System.Data.SqlClient"/> that holds the command of the Sql Connection</param>
        /// <returns></returns>

        public SqlDataReader getBranch(string branchID, SqlCommand cmd)
        {
            cmd.CommandText = "Select * from BranchDetails where BranchID=" + branchID;
            SqlDataReader dr = cmd.ExecuteReader();
            return dr;
        }

        #endregion

        #region IOfficeManager Members

        /// <summary>
        /// To add new branch into the database
        /// </summary>
        /// <param name="branchCode"><see cref="System.String"/> that holds the code of the branch</param>
        /// <param name="branchName"><see cref="System.String"/> that holds the name of the branch</param>
        /// <param name="branchCity"><see cref="System.String"/> that holds the city name of the branch</param>
        /// <param name="branchCCode"><see cref="System.String"/> that holds the city STD code of the branch</param>
        /// <param name="cmd"><see cref="System.Data.SqlClient"/> that holds the command of the Sql connection</param>
        /// <returns>true if the query executed correctly else returns false</returns>

        public bool addBranch(string branchCode, string branchName, string branchCity, string branchCCode, SqlCommand cmd)
        {
            cmd.CommandText = "Insert into BranchDetails values('" + branchCode + "','" + branchName + "','" + branchCity + "','" + branchCCode + "')";
            int res = cmd.ExecuteNonQuery();
            if (res == 1)
                return true;
            else
                return false;
        }

        #endregion

        #region IOfficeManager Members

        /// <summary>
        /// To create a new user account
        /// </summary>
        /// <param name="fName"><see cref="System.String"/> that holds first name of the employee</param>
        /// <param name="lName"><see cref="System.String"/> that holds last name of the employee</param>
        /// <param name="dob"><see cref="System.DateTime"/> that holds date of birth of the employee</param>
        /// <param name="empAddress"><see cref="System.String"/> that holds address of the employee</param>
        /// <param name="empMobile"><see cref="System.String"/> that holds mobile number of the employee</param>
        /// <param name="empStatus"><see cref="System.String"/> that holds the current status of the employee</param>
        /// <param name="branchID"><see cref="System.Int32"/> that holds id of the branch</param>
        /// <param name="cmd"><see cref="System.Data.SqlClient"/> that holds command of the Sql Connection</param>
        /// <returns></returns>

        public bool createAccount(string fName, string lName, DateTime dob, string empAddress, string empMobile, string empStatus, int branchID, SqlCommand cmd)
        {
            cmd.CommandText = "Insert into OfficeStaffDetails values('" + fName.ToLower() + dob.Day + dob.Month + "','Welcome123','" + fName + "','" + lName + "','" + dob.Date + "','" + empAddress + "','" + empMobile + "','" + empStatus + "'," + branchID + ")";
            int res=cmd.ExecuteNonQuery();
            if (res == 1)
                return true;
            else
                return false;
        }

        #endregion

        #region IOfficeManager Members

        /// <summary>
        /// To Change Password for an user
        /// </summary>
        /// <param name="userName"><see cref="System.String"/> that holds the userid of the account holder</param>
        /// <param name="oldPass"><see cref="System.String"/> that holds the existing password of the user</param>
        /// <param name="newPass"><see cref="System.String"/> that holds the new password of the user</param>
        /// <param name="cmd"><see cref="System.Data.SqlClient"/> that holds the command of the Sql Connection</param>
        /// <returns></returns>

        public bool changePassword(string userName, string oldPass, string newPass, SqlCommand cmd)
        {
            cmd.CommandText = "Update OfficeStaffDetails Set Password='" + newPass + "' where EmpCode='" + userName + "' and Password='" + oldPass + "'";
            int res=cmd.ExecuteNonQuery();
            if (res == 1)
                return true;
            else
                return false;
        }

        #endregion

        #region IOfficeManager Members

        /// <summary>
        /// To get scheme details for a particular plan id
        /// </summary>
        /// <param name="planID"><see cref="System.String"/> that holds the id of the plan</param>
        /// <param name="cmd"><see cref="System.Data.SqlClient"/> that hold the command of the Sql Connection</param>
        /// <returns></returns>

        public SqlDataReader getScheme(string planID, SqlCommand cmd)
        {
            cmd.CommandText = "Select * from SchemeDetails where PlanID=" + planID;
            SqlDataReader dr = cmd.ExecuteReader();
            return dr;
        }

        #endregion

        #region IOfficeManager Members

        /// <summary>
        /// To get all the plans from database
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>

        public SqlDataReader getAllPlans(SqlCommand cmd)
        {
            cmd.CommandText = "Select * from PlanDetails";
            SqlDataReader dr = cmd.ExecuteReader();
            return dr;            
        }

        #endregion

        #region IOfficeManager Members

        /// <summary>
        /// To edit an existing scheme into the database
        /// </summary>
        /// <param name="schemeDues"><see cref="System.Int32"/> that holds the number of the dues in that scheme</param>
        /// <param name="planYear"><see cref="System.String"/> that holds the duration of the scheme</param>
        /// <param name="amtInstall"><see cref="System.String"/> that holds the installment amount of the scheme</param>
        /// <param name="amtConsi"><see cref="System.String"/> that holds the consideration amount of the scheme</param>
        /// <param name="amtExpConsi"><see cref="System.String"/> that holds the expected consideration of the scheme</param>
        /// <param name="amtAccConsi"><see cref="System.String"/> that holds the accedential consideration of the scheme</param>
        /// <param name="plotSize"><see cref="System.String"/> that holds the size of the plot</param>
        /// <param name="schemeStatus"><see cref="System.String"/> that holds the current status of the scheme</param>
        /// <param name="planID"><see cref="System.String"/> that holds the id of the plan</param>
        /// <param name="cmd"><see cref="System.Data.SqlClient"/> that holds the command of the Sql Connection</param>
        /// <returns>It returns true when the query executed correctly otherwise returns false.</returns>        

        public bool editScheme(string schemeID, int schemeDues, string planYear, string amtInstall, string amtConsi, string amtExpConsi, string amtAccConsi, string plotSize, string schemeStatus, string planID, SqlCommand cmd)
        {
            cmd.Parameters.Add("@SchemeID", SqlDbType.BigInt).Value = schemeID;
            cmd.Parameters.Add("@SchemeDues", SqlDbType.Int).Value = schemeDues;
            cmd.Parameters.Add("@PlanYear", SqlDbType.NVarChar).Value = planYear;
            cmd.Parameters.Add("@Install", SqlDbType.Money).Value = String.Format("{0:c}", amtInstall);
            cmd.Parameters.Add("@Consi", SqlDbType.Money).Value = String.Format("{0:c}", amtConsi);
            cmd.Parameters.Add("@ExpConsi", SqlDbType.Money).Value = String.Format("{0:c}", amtExpConsi);
            cmd.Parameters.Add("@AccConsi", SqlDbType.Money).Value = String.Format("{0:c}", amtAccConsi);
            cmd.Parameters.Add("@PlotSize", SqlDbType.BigInt).Value = plotSize;
            cmd.Parameters.Add("@Status", SqlDbType.NVarChar).Value = schemeStatus;
            cmd.Parameters.Add("@planID", SqlDbType.Int).Value = planID;
            cmd.Parameters.Add("@Mode", SqlDbType.Int).Value = 2;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "AddScheme";
            int res = cmd.ExecuteNonQuery();
            if (res == 1)
                return true;
            else
                return false;
        }

        #endregion

        #region IOfficeManager Members

        /// <summary>
        /// To add new scheme into the database
        /// </summary>
        /// <param name="designName"><see cref="System.String"/> that holds the name of the designation</param>
        /// <param name="rank"><see cref="System.Int32"/> that holds the rank of the particular designation</param>
        /// <param name="firstYear"><see cref="System.Int32"/> that holds the percentage of the first year commission for the designation</param>
        /// <param name="secondYear"><see cref="System.Int32"/> that holds the percentage of the second year commission for the designation</param>
        /// <param name="thirdYear"><see cref="System.Int32"/> that holds the percentage of the third year commission for the designation</param>
        /// <param name="lumpSum"><see cref="System.Int32"/> that holds the percentage of the lump sum commission for the designation</param>
        /// <param name="cmd"><see cref="System.Data.SqlClient"/> that holds the command of the SQL connection</param>
        /// <returns></returns>

        public bool addDesignation(string designName, int rank, decimal firstYear, decimal secondYear, decimal thirdYear, decimal lumpSum, SqlCommand cmd)
        {
            cmd.CommandText = "Insert into DesignationDetails values('" + designName + "'," + rank + "," + firstYear + "," + secondYear + "," + thirdYear + "," + lumpSum + ")";
            int res=cmd.ExecuteNonQuery();
            if (res == 1)
                return true;
            else
                return false;
        }

        #endregion

        #region IOfficeManager Members

        /// <summary>
        /// To get available designations from database
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>

        public SqlDataAdapter getDesignations(SqlCommand cmd)
        {
            cmd.CommandText = "Select * from DesignationDetails order by Rank";
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            return da;
        }

        #endregion

        #region IOfficeManager Members

        /// <summary>
        /// To edit the designation details into the database
        /// </summary>
        /// <param name="designID"><see cref="System.Int32"/> that holds the id of the designation</param>
        /// <param name="designName"><see cref="System.String"/> that holds the name of the designation</param>
        /// <param name="rank"><see cref="System.Int32"/> that holds the rank of the designation</param>
        /// <param name="firstYear"><see cref="System.Decimal"/> that holds the percentage of the first year for the particular designation</param>
        /// <param name="secondYear"><see cref="System.Decimal"/> that holds the percentage of the second year for the particular designation</param>
        /// <param name="thirdYear"><see cref="System.Decimal"/> that holds the percentage of the third year for the particular designation</param>
        /// <param name="lumpSum"><see cref="System.Decimal"/> that holds the percentage of the lump sum for the particular designation</param>
        /// <param name="cmd"><see cref="System.Data.SqlClient"/> that holds the command of the SQL connection</param>
        /// <returns></returns>

        public bool editDesignation(int designID, string designName, int rank, decimal firstYear, decimal secondYear, decimal thirdYear, decimal lumpSum, SqlCommand cmd)
        {
            cmd.CommandText = "Update DesignationDetails Set DesignationName='" + designName + "', Rank=" + rank + ", IYear=" + firstYear + ", IIYear=" + secondYear + ", IIIYear=" + thirdYear + ", LumpSum=" + lumpSum + " where DesignationCode=" + designID;
            int res = cmd.ExecuteNonQuery();
            if (res == 1)
                return true;
            else
                return false;
        }

        #endregion

        #region IOfficeManager Members

        /// <summary>
        /// To get All Staff Details from Database
        /// </summary>
        /// <param name="cmd"><see cref="System.Data.SqlClient"/> that holds the command of the SQL Connection</param>
        /// <returns></returns>

        public SqlDataAdapter getDownLine(SqlCommand cmd)
        {
            cmd.CommandText = "Select * from EmployeeDetails E Inner Join BranchDetails B On E.BranchId = B.BranchID Order BY IntroducerCode";
                //"Select EmpName, EmpCode, BranchDetails.BranchCityCode from EmployeeDetails, BranchDetails where EmployeeDetails.BranchID=BranchDetails.BranchID Order By BranchCityCode, IntroducerCode, EmpCode";
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            return da;
        }

        #endregion

        #region IOfficeManager Members

        /// <summary>
        /// To get the downline information of a particular person
        /// </summary>
        /// <param name="designID"><see cref="System.String"/>that holds the designation id of an employee</param>
        /// <param name="cmd"><see cref="System.Data.SqlClient"/>that holds the sqlcommand</param>
        /// <returns></returns>

        public SqlDataAdapter getInformations(string designID, SqlCommand cmd)
        {
            cmd.CommandText = "Select B.BranchCityCode+CAST(E.EmpCode AS Varchar) As [Emp Code], E.EmpCode, E.EmpName as [Emp Name], E.Age as [Emp Age], E.PANNo as [PAN No], E.ContactNo as [Contact No], B.BranchName as [Branch Name], B.BranchCity as [Branch City], Convert(varchar, E.DateofJoinCode, 103) as [Date of Join] from EmployeeDetails E, BranchDetails B where IntroducerCode=" + designID + " and E.BranchID=B.BranchID";
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            return da;
        }

        #endregion

        #region IOfficeManager Members

        /// <summary>
        /// To get the policy details of the particular employee
        /// </summary>
        /// <param name="designID"></param>
        /// <param name="cmd"></param>
        /// <returns><see cref="System.SqlClient"/></returns>


        public SqlDataAdapter getCustomers(string designID, SqlCommand cmd)
        {
            cmd.CommandText = "Select C.PolicyID as [Policy Number], C.CustomerName as [Customer Name], C.DateofComm as [Commencement Date], P.PlanName as [Plan Name], P.PlanTerm as [Term], P.PlanType as [Plan Type], S.SchemeDues as [Dues], S.PlanYear as [Plan Year], S.Amount, S.Consideration, S.PlotSize as [Plot Size]  from PolicyDetails C, SchemeDetails S, PlanDetails P where AgentCode=" + designID + " and C.SchemeID=S.SchemeID and S.PlanID=P.PlanID";
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            return da;
        }

        #endregion

        #region IOfficeManager Members
        /// <summary>
        /// To get the upline of an employee...
        /// </summary>
        /// <param name="designID"><see cref="System.String"/> holds employee code of an employee</param>
        /// <param name="cmd"><see cref="System.SqlClient"/> holds the sqlcommand</param>
        /// <returns><see cref="System.SqlClient"/> returns the sqldatareader</returns>

        public SqlDataReader getUpline(string designID, SqlCommand cmd)
        {
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "JV_SP_EmpUpLine";
                cmd.Parameters.Add("@EmpCode", SqlDbType.BigInt).Value = designID;
                SqlDataReader dr = cmd.ExecuteReader();
                return dr;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region IOfficeManager Members

        /// <summary>
        /// To get the office staff details
        /// </summary>
        /// <param name="userID"><see cref="System.String"/> that holds the user id</param>
        /// <param name="cmd"><see cref="System.Data.SqlClient"/> that holds the sql command for db connection</param>
        /// <returns><see cref="System.Data.SqlClient"/> that returns the data reader of office staff details</returns>
        
        public SqlDataReader getOfficeStaffDetails(string userID, SqlCommand cmd)
        {
            cmd.CommandText = "SELECT (O.EmpFName+' '+O.EmpLName) AS Name, O.DateofBirth, B.BranchID, B.BranchCity FROM OfficeStaffDetails O, BranchDetails B where O.BranchID=B.BranchID AND EmpCode='" + userID + "'";
            SqlDataReader dr = cmd.ExecuteReader();
            return dr;
        }

        #endregion

    }
}
